package Test4;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;
import java.util.ResourceBundle;

public class JDBCUile {

	private static ResourceBundle rb = ResourceBundle.getBundle("Test4.jdbc");
	private static String classname = rb.getString("driver");
	private static String url = rb.getString("url");
	private static String username = rb.getString("username");
	private static String password = rb.getString("password");

	static {
		try {
			Class.forName(classname);
			System.out.println("驱动加载成功");
		} catch (ClassNotFoundException e) {
			// TODO Auto-generated catch block
			System.out.println("驱动加载失败");
			e.printStackTrace();
		}

	}

	public static void exClose(ResultSet resultSet, Statement statement, Connection connection) {
		if (connection != null) {
			try {
				connection.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		if (statement != null) {
			try {
				statement.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		if (resultSet != null) {
			try {
				resultSet.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}

	}

	// 获得连接
	public static Connection getConnection() {
		Connection connection = null;
		try {
			connection = DriverManager.getConnection(url, username, password);
			// System.out.println("数据库连接成功");
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			System.err.println("数据库连接失败");
			e.printStackTrace();
		}
		return connection;

	}
	// 插入 单个水果

	public void insertFruits(Fruits fruit) {
		String sql = "insert into fruits (name,selling_price,cost_price,repertory) values(?,?,?,?)";
		Connection connection = getConnection();
		PreparedStatement ps = null;
		try {
			ps = connection.prepareStatement(sql);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		String name = fruit.getName();
		
		double sellingPrice = fruit.getSellingPrice();
		double costPrice = fruit.getCostPrice();
		double repertory = fruit.getRepertory();

		int r = 0;
		try {
			ps.setString(1, name);
			
			ps.setDouble(2, sellingPrice);
			ps.setDouble(3, costPrice);
			ps.setDouble(4, repertory);
			r = ps.executeUpdate();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}

		exClose(null, ps, connection);

	}

	// 插入 进货单

	public void insertPurches(PurchesList purchesList) {
		String sql = "insert into purches (name,amount,cost_price,total,price) values(?,?,?,?,?)";
		Connection connection = getConnection();
		PreparedStatement ps = null;
		try {
			ps = connection.prepareStatement(sql);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		String name = purchesList.getName();
		double amount = purchesList.getAmount();
		double costPrice = purchesList.getCostPrice();
		double total = purchesList.getTotal();
		double price = purchesList.getPrice();

		int r = 0;
		try {
			ps.setString(1, name);
			ps.setDouble(2, amount);
			ps.setDouble(3, costPrice);
			ps.setDouble(4, total);
			ps.setDouble(5, price);
			r = ps.executeUpdate();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}

		exClose(null, ps, connection);

	}

	// 插入售卖
	public void insertRecord(Record record) {
		String sql = "insert into record(name,weight,profit,time) values(?,?,?,?)";
		Connection connection = getConnection();
		PreparedStatement ps = null;
		try {
			ps = connection.prepareStatement(sql);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}

		String name = record.getName();
		double weight = record.getWeight();
		double profit = record.getProfit();
		String time = record.getTime();

		try {
			ps.setString(1, name);
			ps.setDouble(2, weight);
			ps.setDouble(3, profit);
			ps.setString(4, time);

			ps.executeUpdate();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}

		exClose(null, ps, connection);

	}

	// 按照条件查询水果信息
	public static Fruits selectFruits(String name) {

		String sql = "select name,selling_price,cost_price,repertory from fruits where name=? ";

		Connection connection = null;

		ResultSet rs = null;
		connection = getConnection();

		PreparedStatement ps = null;
		Fruits fruits = new Fruits();

		try {
			ps = connection.prepareStatement(sql);

			ps.setString(1, name);

			rs = ps.executeQuery();
 
			while (rs.next()) {
				fruits.setName(rs.getString("name"));
				fruits.setSellingPrice(rs.getDouble("selling_price"));
				fruits.setCostPrice(rs.getDouble("cost_price"));
				fruits.setRepertory(rs.getDouble("repertory"));

			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}

		exClose(rs, ps, connection);

		return fruits;

	}

	// 修改水果的库存

	public static void updateFuits(String name, double weight) {

		String sql = "update fruits set repertory=? where name=?";

		Connection connection = null;
		connection = getConnection();

		PreparedStatement ps = null;
		try {
			ps = connection.prepareStatement(sql);
			ps.setDouble(1, weight);

			ps.setString(2, name);

			ps.executeUpdate();
			System.out.println("水果库存更新成功");
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}

		exClose(null, ps, connection);

	}

	// 查询某图书的借阅记录
	public static ArrayList<Record> selectRecords() {

		String sql = "select * from record";

		Connection connection = null;

		ResultSet rs = null;
		connection = getConnection();

		PreparedStatement ps = null;

		ArrayList<Record> records = new ArrayList<>();
		try {
			ps = connection.prepareStatement(sql);


			rs = ps.executeQuery();
			while (rs.next()) {
				Record record = new Record();
				record.setName(rs.getString("name"));
				record.setWeight(rs.getDouble("weight"));
				record.setProfit(rs.getDouble("profit"));
				record.setTime(rs.getString("time"));
				records.add(record);

			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}

		exClose(rs, ps, connection);

		return records;

	}

	// 查询某水果的借阅记录
	public static ArrayList<Fruits> selectAllFruits() {

		String sql = "select * from fruits";

		Connection connection = null;

		ResultSet rs = null;
		connection = getConnection();

		ArrayList<Fruits> fruits = new ArrayList<>();

		PreparedStatement ps = null;

		try {
			ps = connection.prepareStatement(sql);
		
			

			rs = ps.executeQuery();

			while (rs.next()) {
				Fruits fruit = new Fruits();
				fruit.setName(rs.getString("name"));
			
				fruit.setSellingPrice(rs.getDouble("selling_price"));
				fruit.setCostPrice(rs.getDouble("cost_price"));
				fruit.setRepertory(rs.getDouble("repertory"));
                  fruits.add(fruit);
			}
			
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}

		exClose(rs, ps, connection);

		return fruits;

	}



	public static int updateFruits(Fruits fruit) {
		
		
		String sql = "update fruits set repertory=? where name =?";
        String name = fruit.getName();
        double repertory = fruit.getRepertory();
		
		
		Connection connection = null;
		connection = getConnection();

		PreparedStatement ps = null;
		int flag = 0;
		try {
			ps = connection.prepareStatement(sql);
			ps.setDouble(1, repertory);
			ps.setNString(2, name);
			
			flag = ps.executeUpdate();

			System.out.println("水果库存更新");
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			System.out.println("更新失败");
			e.printStackTrace();
		}

		exClose(null, ps, connection);
		return flag;

	}



	// 按照销量进行排序
	public static ArrayList<Record> selectAllRecords() {

		String sql = "select name,sum(weight)as sum from record\r\n" + 
				"group by name\r\n" + 
				"order by sum DESC\r\n" + 
				"; " ;
		Connection connection = getConnection();

		PreparedStatement ps = null;
		ResultSet rs = null;
		ArrayList<Record> records = new ArrayList<>();
		try {
			ps = connection.prepareStatement(sql);
			rs = ps.executeQuery();

			while (rs.next()) {
				
				String name = rs.getString("name");
				double sum = rs.getDouble("sum");
				
				
				Record record = new Record();
				record.setName(name);
				
				record.setWeight(sum);
				
				records.add(record);

			}
			ps = connection.prepareStatement(sql);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}

		exClose(rs, ps, connection);
		return records;

	}
	
	// 按照時間段查詢
		public static double selectAllRecordsBytime(String time) {

			String sql = "select sum(profit)as sum from record\r\n" + 
					"where time = ? group by time" ;
			Connection connection = getConnection();

			PreparedStatement ps = null;
			ResultSet rs = null;
			 double sum =0;
			try {
				ps = connection.prepareStatement(sql);
				ps.setString(1, time); 
				
				rs = ps.executeQuery();

				while (rs.next()) {
					
                sum = rs.getDouble("sum");
				}
				ps = connection.prepareStatement(sql);
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}

			exClose(rs, ps, connection);
			return  sum;

		}
		

}
